import json, duckdb, os, uuid
from utils.path_utils import get_project_root
from flask import Blueprint, request, jsonify, Response
from config import ini_op
from time import strftime
from service.rpa import rpa_run
from service.sjcl import sjcl_pz
from database.duckdb.duckdb_base import get_connection

# app = Flask(__name__)
# 创建 Blueprint
rpa_pzsc_blueprint = Blueprint('pzsc', __name__)


# 凭证上传配置
@rpa_pzsc_blueprint.route('/pzsc', methods=['GET', 'POST'])
def rpa_pzsc():
    data = ini_op.getjson('pz_config')
    # 返回JSON响应
    data = json.dumps(data, ensure_ascii=False)
    return Response(data, content_type="application/json")

# 凭证上传预览
@rpa_pzsc_blueprint.route('/pzsc/yl', methods=['GET', 'POST'])
def rpa_pzsc_yl():
    conn = get_connection()
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM t_yl_pz")
    # 获取所有行
    rows = cursor.fetchall()
    # 获取列名
    column_names = [description[0] for description in cursor.description]
    # 将行数据转换为字典列表，并转换 UUID 为字符串
    log = []
    for row in rows:
        row_dict = dict(zip(column_names, row))
        for key, value in row_dict.items():
            if isinstance(value, uuid.UUID):  # 检查是否是 UUID 类型
                row_dict[key] = str(value)  # 转换为字符串
        log.append(row_dict)
    conn.close()
    # 返回JSON响应
    data = {
        "rows": log
    }
    # print(data)
    data = json.dumps(data, ensure_ascii=False)
    return Response(data, content_type="application/json")

# 凭证删除
@rpa_pzsc_blueprint.route('/pzsc/delete', methods=['POST'])
def rpa_pzsc_delete():
    dwbh, dwid, zbid, zbbh = ini_op.getinivalue('basic_config', 'dwbh', 'dwid', 'ledger', 'zbbh')
    data = request.json
    rows = data['rows']
    for row in rows:
        pzny = row['会计年度']+row['会计期间']
        con = get_connection()
        con.execute("DELETE FROM T_API_PZ WHERE LEDGERBH = "+zbbh+" and accdocdate like '"+pzny+"%'")
        con.execute("""
                delete from T_API_PZFL where ACCDOCID not in (select id from T_API_PZ);
                delete from T_API_FZYS where ACCDOCID not in (select id from T_API_PZ);
               """)
        con.close()
    return jsonify({"code": 200, "message": "删除成功"}), 200

@rpa_pzsc_blueprint.route('/pzsc/deleteall', methods=['POST'])
def rpa_pzsc_deleteall():
    dwbh, dwid, zbid, zbbh = ini_op.getinivalue('basic_config', 'dwbh', 'dwid', 'ledger', 'zbbh')
    con = get_connection()
    con.execute("DELETE FROM T_API_PZ WHERE LEDGERBH = "+zbbh)
    con.execute("""
            delete from T_API_PZFL where ACCDOCID not in (select id from T_API_PZ);
            delete from T_API_FZYS where ACCDOCID not in (select id from T_API_PZ);
           """)
    con.close()
    return jsonify({"code": 200, "message": "删除成功"}), 200

# 凭证上传统计
@rpa_pzsc_blueprint.route('/pzsc/tj', methods=['GET', 'POST'])
def rpa_pzsc_tj():
    dwbh, dwid, zbid, zbbh = ini_op.getinivalue('basic_config', 'dwbh', 'dwid', 'ledger', 'zbbh')
    conn = get_connection()
    cursor = conn.cursor()
    cursor.execute("""SELECT uuid() id, '凭证' as 类别, 
                        substr(accdocdate, 1, 4) as 会计年度,
                        substr(accdocdate, 5, 2) as 会计期间,
                        count(*) as 数量
                        FROM T_API_PZ 
                        where LEDGERBH = $p_zhbh
                        group by 类别, 会计年度, 会计期间
                        order by 会计年度, 会计期间""", {'p_zhbh': zbbh})
    # 获取所有行
    rows = cursor.fetchall()
    # 获取列名
    column_names = [description[0] for description in cursor.description]
    # 将行数据转换为字典列表，并转换 UUID 为字符串
    log = []
    for row in rows:
        row_dict = dict(zip(column_names, row))
        for key, value in row_dict.items():
            if isinstance(value, uuid.UUID):  # 检查是否是 UUID 类型
                row_dict[key] = str(value)  # 转换为字符串
        log.append(row_dict)
    conn.close()
    # 返回JSON响应
    data = {
        "rows": log
    }
    # print(data)
    data = json.dumps(data, ensure_ascii=False)
    return Response(data, content_type="application/json")


# 凭证上传更新配置
@rpa_pzsc_blueprint.route('/pzsc/update', methods=['GET', 'POST'])
def rpa_pzsc_update():
    data = request.json
    ini_op.opinivalue('pz_config', **data)
    # 返回JSON响应
    data = json.dumps({"message": "success"}, ensure_ascii=False)
    return Response(data, content_type="application/json")


# 凭证上传运行
@rpa_pzsc_blueprint.route('/pzsc/run', methods=['GET', 'POST'])
def rpa_pzsc_run():
    data = request.json
    timenow = str(strftime("%Y%m%d%H%M%S"))
    ini_op.opinivalue('pz_config', **data)
    if data['mode'] == '机器人取数':
        rpa_run.run('pzsc')
    else:
        dwbh, dwid, zbid, zbbh = ini_op.getinivalue('basic_config', 'dwbh', 'dwid', 'ledger', 'zbbh')
        code = sjcl_pz.run(dwbh, dwid, zbid, zbbh, '', '', data['filepath'], timenow, 'sd')
        if code == 400:
            return jsonify(error="File data error"), 400
    # 返回JSON响应
    data = json.dumps({"message": "success"}, ensure_ascii=False)
    return Response(data, content_type="application/json")